On-Line Analytical Processing (OLAP) database servers provide a platform for high-end analytics and decision support applications. OLAP technology allows organizations (i.e., enterprises) to extract data from multiple, disparate transactional and operational systems into homogenous, aggregated repositories of information for the purpose of high-performance analysis and reporting. The process of moving data out of core transactional and operational systems into an OLAP environment typically includes cleansing the data by enforcing consistency and data integrity, and aggregating the data using like keys. This process is known as data transformation. The result of the data transformation process is a multi-dimensional OLAP database, known as an “OLAP cube”, which is highly optimized for reporting and analysis.
OLAP cubes are typically subject-matter oriented, with the data in the cube being organized by a series of dimensions. The dimensions are hierarchical in nature (e.g., year, quarter, month, etc.) to allow for multiple levels of data aggregation. An end user can thus choose whether to analyze data in an OLAP cube at varying levels of data aggregation, such as at summary, intermediate and detailed analysis levels. OLAP data cubes with different subject matters typically have different numbers of dimensions with different hierarchical designs. OLAP cubes can also share one or more dimensions to provide a homogenous view of the structure of the cube data amongst different cubes. The concept of keying cube data by dimensions, known as dimensional modeling, allows for a simplified approach to reporting and analysis of data. In particular, users need only understand the dimensions of the OLAP cubes and their hierarchies, and are insulated from the need to understand the underlying physical implementation of the database schema that is used to store the data. Thus, users are presented with a logical view of the information stored in the cube, and can create reports and queries without having to perform complex joins or any other function which would require an understanding of the underlying database structure.
The leading OLAP database servers on the market today are the Hyperion Essbase™ OLAP server, available from Hyperion Solutions Corp. of CA, and the Microsoft Analysis Services OLAP server, available from Microsoft Corp. of WA. Both servers provide the ability to build subject matter cubes using dimensional modeling techniques which support hierarchies, calculated members, aggregations, and high-performance reporting and analysis. From a querying standpoint, both servers offer similar features, but with very different implementations. In particular, the structured query formats used for the two database servers are very different. The query language used for the Microsoft Analysis Services OLAP server is known as Multi-Dimensional Expression Query Language (“MDX”). The MDX query language leverages Microsoft's OLE database (“OLEDB”) for OLAP standard, and follows a Structured Query Language (SQL)-like syntax with special extensions that are used for OLAP cube querying. The special extensions are needed since SQL is only two-dimensional, while OLAP data cubes are n-dimensional. The query language that is used for the Hyperion Essbase™ OLAP server is known as Report Scripts (“RS”). Although the RS query language provides a full range of querying options within the Hyperion Essbase™ environment, the syntax of queries for RS is very different from the syntax of queries for MDX. For example, exemplary MDX and RS query statements 100 are shown in FIG. 1 (note the data cubes used for the two examples contained different data). A comparison shows that the syntax used to create these query statements is very different. The differences between these formats makes it difficult for OLAP query and reporting applications to support both Microsoft Analysis Services and Hyperion Essbase™ OLAP servers from the same code base.
Along with the Microsoft Analysis Services and Hyperion Essbase™ OLAP database servers, there are other OLAP servers on the market today that use other structured query formats. Further, there are likely to be still other OLAP servers on the market in the future that will use still other structured query formats, including formats that are not yet known. The differences in structured query formats between these present and future OLAP servers makes it even more difficult for OLAP query and reporting applications to support any or all of these different servers. For example, it would be very difficult to design an OLAP query and reporting application that can be easily adapted to support a future OLAP server that will use a structured query format that is as yet unknown.
In the relational database world, the SQL and Open Database Connectivity (“ODBC”) standards are industry-standard methodologies for accessing relational databases. Thus, query and reporting applications in the relational database world can implement SQL and ODBC methodologies to access relational databases across relational database vendors. Unfortunately, industry-standard methodologies do not exist for use with OLAP database technology. In particular, there are currently no SQL or ODBC-type standards that simplify the generation of OLAP queries. Thus, it is difficult for an OLAP query and reporting application to support both Microsoft Analysis Services and Hyperion Essbase™ servers from one code base, or to support other combinations of OLAP servers that use, or will use, different structured query formats.
Thus, there is a need for a method and apparatus for generating OLAP queries which can be used by OLAP query and reporting applications to efficiently and easily support multiple OLAP database servers that use different structured query formats. For example, there is a need for a method and apparatus for generating OLAP queries which can be used by OLAP query and reporting applications to support both Microsoft Analysis Services and Hyperion Essbase™ database servers from one code base. There is also a need for a method and apparatus for generating OLAP queries which can be used by OLAP query and reporting applications to support other OLAP servers on the market today that use different structured query formats, or that can be easily adapted to support OLAP servers that will be on the market in the future.